package top.went.db.dao;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import top.went.pojo.DeliverDetailEntity;

import java.util.List;

/**
 * 发货明细/交付记录
 */
public interface OrderDeliverDetail extends CrudRepository<DeliverDetailEntity, Integer> {
//    @Query("select count(d) from DeliverDetailEntity d where ")
//    Long countDeliver(Integer id);

    /**
     * 已经支付产品数量
     * @param pfId
     * @param id
     * @return
     */
    @Query(nativeQuery = true,value = "select (select nvl(sum(d.dd_Number),0) from TB_DELIVER_DETAIL d " +
            "where d.ORDER_ID =?2 and d.PRODUCT_ID =?1 and d.DD_STATUS!=-1) - (select nvl(sum(r.RGD_NUM),0) " +
            "from TB_RETURN_GOODS_DETAIL r left join TB_RETURN_GOODS g on r.RG_ID = g.RG_ID where g.ORDER_ID = ?2" +
            " and r.PF_ID = ?1) from dual")
    Long countAllOrder(Integer pfId, Integer id);

    @Query("from DeliverDetailEntity d where d.tbOrderByOrderId.orderId = ?1")
    List<DeliverDetailEntity> findAllByOrder(Integer id);

    /**
     * 已经发货产品数量
     * @param pfId
     * @param intValue
     * @return
     */
    @Query(nativeQuery = true,value = "select (select nvl(sum(d.dd_Number),0)" +
            " from TB_DELIVER_DETAIL d where d.ORDER_ID =?2 and d.PRODUCT_ID =?1 and d.DD_STATUS=1) " +
            "- (select nvl(sum(r.RGD_NUM),0) from TB_RETURN_GOODS_DETAIL r left join TB_RETURN_GOODS g " +
            "on r.RG_ID = g.RG_ID where g.ORDER_ID = ?2 and r.PF_ID = ?1) from dual"
    )
    Long countAllOrderSend(Integer pfId, int intValue);
}
